package com.opennews.openplatform.service;

import com.fasterxml.jackson.databind.ObjectMapper;
import com.opennews.openplatform.dto.bank.card.CreateBankCardDto;
import com.opennews.openplatform.dto.bank.card.UpdateBankCardDto;
import com.opennews.openplatform.entity.tables.records.BankCardRecord;
import com.opennews.openplatform.jooq.DSLContextWrapper;
import jakarta.validation.Valid;
import lombok.RequiredArgsConstructor;
import org.jooq.DSLContext;
import org.springframework.stereotype.Service;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import static com.opennews.openplatform.entity.Tables.*;

@RequiredArgsConstructor
@Service
public class BankCardService {
    private final DSLContext dslContext;
    private final DSLContextWrapper dslContextWrapper;
    private final BankService bankService;
    private final IncomeExpenseDetailService incomeExpenseDetailService;

    /**
     * Queries all bank cards basic info which associated with provided account group.
     *
     * @param accountGroupId: The string of the user account group id.
     * @param pageSize:       How many records is return in one trip.
     * @param pageIndex:      Where to take rows.
     * @return The list of map instance which contains keys below.
     * id: String of bank card id.
     * name: String of bank card name.
     * isCreditCard: Bool to indicate if this is a credit card.
     * enabled: Bool to indicate if this card is enabled.
     * statementDate: Number string of the day of statement.
     * bankName: String of the bank name.
     * bankShortName: String of bank short name.
     * bankLogoUrl: String of bank logo url.
     */
    public List<Map<String, Object>> queryBasicInfoByAccountGroup(String accountGroupId, Integer pageSize, Integer pageIndex) {
        // Queries bank card by account group id.
        var query = dslContext.select(
                        BANK_CARD.ID.as("id"),
                        BANK_CARD.NAME.as("name"),
                        BANK_CARD.IS_CREDIT_CARD.as("isCreditCard"),
                        BANK_CARD.ENABLED.as("enabled"),
                        BANK_CARD.STATEMENT_DATE.as("statementDate"),
                        BANK.SHORT_NAME.as("bankShortName"),
                        BANK.NAME.as("bankName")
                )
                .from(BANK_CARD)
                .join(BANK).on(BANK_CARD.BANK_ID.eq(BANK.ID))
                .where(BANK_CARD.ACCOUNT_GROUP_ID.eq(accountGroupId))
                .orderBy(BANK_CARD.BANK_ID);

        if (pageSize != null && pageIndex != null) {
            query.limit(pageSize)
                    .offset(pageIndex * pageSize);
        }

        return query.fetch()
                .map(p -> {
                    Map<String, Object> item = p.intoMap();
                    item.put("bankLogoUrl", bankService.getLogoUrl(item.get("bankShortName").toString()));

                    return item;
                });
    }

    /**
     * Queries all bank cards with basic info which associated with provided account group.
     *
     * @param accountGroupId: The string of the user account group id.
     * @return List of map instance which contains keys below.
     * id: String of bank card id.
     * name: String of bank card name.
     * isCreditCard: Bool to indicate if this is a credit card.
     * enabled: Bool to indicate if this card is enabled.
     * statementDate: Number string of the day of statement.
     * bankName: String of the bank name.
     * bankShortName: String of bank short name.
     * bankLogoUrl: String of bank logo url.
     */
    public List<Map<String, Object>> queryAllWithBasicInfoByAccountGroup(String accountGroupId) {
        return queryBasicInfoByAccountGroup(accountGroupId, null, null);
    }

    /**
     * Queries bank card based on provided id.
     *
     * @param accountGroupId: The string of user account group id.
     * @param id:             The string of bank card id.
     * @return The map instance which contains keys below.
     * id: String of bank card id.
     * bankId: String of bank id.
     * bankName: String of the bank name which card belongs to.
     * bankShortName: String of the bank short name.
     * bankLogoUrl: String of the bank logo url.
     * statementDate: The statement date of the card if it is credit card.
     * paymentDueDate: The payment due date of the card if it is credit card.
     * customerServiceNumber: String of the card customer service phone number.
     * isCreditCard: Bool of if this card is credit card.
     * enabled: Bool to indicate if this card is enabled.
     */
    public Map<String, Object> queryBankCardInfo(String accountGroupId, String id) {
        Map<String, Object> bankInfo = dslContext.select(
                        BANK_CARD.ID.as("id"),
                        BANK.ID.as("bankId"),
                        BANK.NAME.as("bankName"),
                        BANK.SHORT_NAME.as("bankShortName"),
                        BANK_CARD.NAME.as("name"),
                        BANK_CARD.STATEMENT_DATE.as("statementDate"),
                        BANK_CARD.PAYMENT_DUE_DATE.as("paymentDueDate"),
                        BANK_CARD.CUSTOMER_SERVICE_NUMBER.as("customerServiceNumber"),
                        BANK_CARD.IS_CREDIT_CARD.as("isCreditCard"),
                        BANK_CARD.ENABLED.as("enabled")
                )
                .from(BANK_CARD)
                .join(BANK).on(BANK_CARD.BANK_ID.eq(BANK.ID))
                .where(
                        BANK_CARD.ACCOUNT_GROUP_ID.eq(accountGroupId)
                                .and(BANK_CARD.ID.eq(id))
                )
                .fetchOneMap();

        bankInfo.put("bankLogoUrl", bankService.getLogoUrl(bankInfo.get("bankShortName").toString()));

        return bankInfo;
    }

    /**
     * Adds new bank card for account group.
     *
     * @param accountGroupId: The string of user account group id.
     * @param data:           The map which contains keys with same name as bank card properties.
     * @param accountGroupId: The string of user account group id.
     */
    public void add(String accountGroupId, @Valid CreateBankCardDto data) {
        // Creates new BankCard with posted data.
        BankCardRecord bankCard = dslContext.newRecord(BANK_CARD);
        bankCard.from(data);
        bankCard.setAccountGroupId(accountGroupId);
        bankCard.setBankId(data.getBank().getId());
        bankCard.insert();
    }

    /**
     * Updates bank card info based on provided map data and its id.
     * The map keys must match bank card property names.
     * If the map item will be ignored if its value is null.
     *
     * @param accountGroupId: The string of user account group id.
     * @param data            The UpdateBankCardDto which contains keys with same name as bank card properties.
     */
    public void update(String accountGroupId, @Valid UpdateBankCardDto data) {
        if (dslContextWrapper.count(BANK_CARD, BANK_CARD.ID.eq(data.getId())
                .and(BANK_CARD.ACCOUNT_GROUP_ID.eq(accountGroupId))) == 1) {
            BankCardRecord record = dslContext.newRecord(BANK_CARD);
            record.from(data);
            record.setBankId(data.getBank().getId());

            // Retains the `id` for the WHERE clause but excludes it from the SET clause.
            record.changed(BANK_CARD.ID, false);

            record.update();
        }
    }

    /**
     * Deletes the BankCard with provided id.
     *
     * @param accountGroupId: The string of the user account group id.
     * @param id:             The bank card id.
     */
    public boolean delete(String accountGroupId, String id) {
        boolean isDeleted = false;

        // Queries the input id and makes sure it belongs to the specific account group.
        // This is an example shows how to secure the data by taking extra action.
        // This can void other account group users call api directly and provide id which they don't actually own it.
        // AKA parallel authentication.
        // Checks if this bank card is used by income/expense.
        if (dslContextWrapper.count(INCOME_EXPENSE_DETAIL, INCOME_EXPENSE_DETAIL.ACCOUNT_GROUP_ID.eq(accountGroupId)
                .and(INCOME_EXPENSE_DETAIL.BANK_CARD_ID.eq(id))) == 0) {
            // If this bank card is not used then deletes it.
            dslContext.deleteFrom(BANK_CARD)
                    .where(
                            BANK_CARD.ACCOUNT_GROUP_ID.eq(accountGroupId)
                                    .and(BANK_CARD.ID.eq(id))
                    )
                    .execute();

            isDeleted = true;
        }

        return isDeleted;
    }

    /**
     * Deletes the BankCards with provides list of id.
     *
     * @param accountGroupId: The string of the user account group id.
     * @param ids:            The string list of id.
     */
    public void delete(String accountGroupId, List<String> ids) {
        // Queries the input id list and makes sure they belong to the specific account group.
        // This is an example shows how to secure the data by taking extra action.
        // This can void other account group users call api directly and provide these ids which they don't actually own them.
        // AKA parallel authentication.
        ids = queryAccountGroupBankCardIds(ids, accountGroupId);

        // Queries all bank card ids which used by income/expense.
        List<String> usedIds = incomeExpenseDetailService.queryUsedBankCardIds(ids, accountGroupId);

        // Excludes all used bank card ids.
        for (String usedId : usedIds) {
            ids.remove(usedId);
        }

        if (!ids.isEmpty()) {
            // Executes batch deleting by DetachedCriteria to delete any unused bank card.
            dslContext.deleteFrom(BANK_CARD)
                    .where(BANK_CARD.ID.in(ids))
                    .execute();
        }
    }

    /**
     * Queries the input id list and makes sure they belong to the specific account group.
     *
     * @param accountGroupId: The string of the user account group id.
     * @param ids:            The string list of id.
     * @return The matched id list.
     */
    private List<String> queryAccountGroupBankCardIds(List<String> ids, String accountGroupId) {
        if (!ids.isEmpty()) {
            // Queries the ids match the account group.
            return dslContext.selectFrom(BANK_CARD)
                    .where(
                            BANK_CARD.ACCOUNT_GROUP_ID.eq(accountGroupId)
                                    .and(BANK_CARD.ID.in(ids))
                    )
                    .fetch(BANK_CARD.ID);
        } else {
            return new ArrayList<>();
        }
    }
}
